﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using Microsoft.Win32;
using ExcelDataReader;
using System.IO;
using System.Data;
using OfficeOpenXml;
using System.Configuration;
using ClosedXML.Excel;
using black_screen_mode_server.Models;


namespace black_screen_mode_server.Pages
{
    /// <summary>
    /// MonitoringPage.xaml 的交互逻辑
    /// </summary>
    public partial class MonitoringPage : Page
    {
        public MonitoringPage()
        {
            InitializeComponent();
        }

        //添加分组
        private void btnListSetting_Click(object sender, RoutedEventArgs e)
        {
            GroupWindow groupWindow = new GroupWindow();
            groupWindow.Show();
        }

        //连接数据库
        private string connectionString = ProjectTools.configDbConnString;//"server=LAPTOP-CKCFDD72;Database=TestDatabase;User Id=sa;Password=bageling;";

        //导出为Excel文件
        private void btnExportBlackList_Click(object sender, RoutedEventArgs e)
        {
            DataTable dataTable = GetDataFromDatabase(0);
            DataTable dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("GroupName", typeof(string));
            dt.Columns.Add("AreaName", typeof(string));
            dt.Columns.Add("Category", typeof(string));
            dt.Columns.Add("EventType", typeof(string));
            dt.Columns.Add("Block", typeof(string));
            dt.Columns.Add("Source", typeof(string));
            dt.Columns.Add("ListType", typeof(int));

            // 将数据转换为Excel
            using (var workbook = new XLWorkbook())
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    DataRow dtRow = dt.NewRow();
                    dtRow["Id"] = row["Id"];
                    dtRow["GroupName"] = row["GroupName"];
                    dtRow["AreaName"] = row["AreaName"];
                    if (CategoryConstant.CategoryIntMap.ContainsKey(Convert.ToInt32(row["Category"])))
                    {
                        dtRow["Category"] = CategoryConstant.CategoryIntMap[Convert.ToInt32(row["Category"])];
                    }
                    dtRow["EventType"] = row["EventType"];
                    dtRow["Block"] = row["Block"];
                    dtRow["Source"] = row["Source"];
                    dtRow["ListType"] = row["ListType"];
                    dt.Rows.Add(dtRow);
                }
                var worksheet = workbook.Worksheets.Add(dt, "BlackList");
                SaveExcelFile(workbook);
            }
        }

        //从blacke_list读取数据
        private DataTable GetDataFromDatabase(Int16 listType)
        {
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = $"SELECT * FROM list where ListType ={listType}";
                    using (SqlCommand cmd = new SqlCommand(query, connection))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(dataTable);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error!" + ex.Message);
                }
            }
            return dataTable;
        }

        //导出Excel文档
        private void SaveExcelFile(XLWorkbook workbook)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog
            {
                Filter = "Excel 文件（*.xlsx）|*.xlsx",
                Title = "另存文件为"
            };
            try
            {
                if (saveFileDialog.ShowDialog() == true)
                {
                    workbook.SaveAs(saveFileDialog.FileName);
                    MessageBox.Show("导出成功!", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("提示" + ex.Message, "提示");
            }

        }



        //导入文件(需要模板-->与white_list列对应)
        private void btnImportBlackList_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";

            if (openFileDialog.ShowDialog() == true)
            {
                string filePath = openFileDialog.FileName;

                // 调用导入数据的方法，传递文件路径
                ImportDataFromExcel(filePath,0);
            }
        }

        private void ImportDataFromExcel(string filePath, Int16 listType)
        {
            FileInfo excelFile = new FileInfo(filePath);
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(excelFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                int columns = worksheet.Dimension.End.Column;

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    try
                    {
                        connection.Open();
                        using (SqlCommand clearCommand = new SqlCommand($"DELETE FROM list where ListType ={listType}", connection))
                        {
                            clearCommand.ExecuteNonQuery();
                        }

                        string insertQuery = $"INSERT INTO list VALUES ({string.Join(",", Enumerable.Range(0, columns).Select(i => "@" + i))})";
                        using (SqlCommand command = new SqlCommand(insertQuery, connection))
                        {

                            for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
                            {
                                command.Parameters.Clear();
                                for (int col = 1; col <= columns; col++)
                                {
                                    if (col == 1)
                                    {
                                        command.Parameters.AddWithValue("@" + (col-1), Convert.ToInt64(worksheet.Cells[row, col].Value));
                                    }
                                    else if(col == 4)
                                    {
                                        string category = worksheet.Cells[row, col].Value?.ToString();
                                        if (CategoryConstant.categoryStrMap.ContainsKey(category))
                                             command.Parameters.AddWithValue("@" + (col-1), CategoryConstant.categoryStrMap[category]);
                                        else
                                            command.Parameters.AddWithValue("@" + (col-1), 0);
                                    }
                                    else if(col == columns)
                                    {
                                        command.Parameters.AddWithValue("@" + (col-1), listType);
                                    }
                                    else
                                    {
                                        command.Parameters.AddWithValue("@" + (col-1), worksheet.Cells[row, col].Value?.ToString());
                                    }
                                  

                                    //id转为整数

                                    //category转为整数

                                    //ListType为
                                }

                                command.ExecuteNonQuery();
                            }
                        }
                        MessageBox.Show("导入成功!", "提示");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("提示" + ex.Message, "提示");
                    }

                }
            }
        }


        ////黑名单搜索功能
        //private void btnFindTag_Click(object sender, RoutedEventArgs e)
        //{

        //    string input = txtFindTag.Text.Trim();

        //    string query = "SELECT Name FROM group WHERE id in (select group_id from black_list where tag= @tag)";

        //    using (SqlConnection connection = new SqlConnection(connectionString))
        //    {
        //        SqlCommand command = new SqlCommand(query, connection);
        //        command.Parameters.AddWithValue("@Tag", input);

        //        connection.Open();
        //        object result = command.ExecuteScalar();

        //        if (result != null)
        //        {
        //            string name = result.ToString();
        //            MessageBox.Show($"找到对应点位，点位分组为：{name}", "提示", MessageBoxButton.OK);
        //        }
        //        else
        //        {
        //            MessageBox.Show("未找到对应记录", "提示", MessageBoxButton.OK);
        //        }
        //    }

        //}

        private void btnImportWhiteList_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";

            if (openFileDialog.ShowDialog() == true)
            {
                string filePath = openFileDialog.FileName;

                // 调用导入数据的方法，传递文件路径
                ImportDataFromExcel(filePath, 1);
            }
        }

        private void btnExportWhiteList_Click(object sender, RoutedEventArgs e)
        {
            DataTable dataTable = GetDataFromDatabase(1);
            DataTable dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("GroupName", typeof(string));
            dt.Columns.Add("AreaName", typeof(string));
            dt.Columns.Add("Category", typeof(string));
            dt.Columns.Add("EventType", typeof(string));
            dt.Columns.Add("Block", typeof(string));
            dt.Columns.Add("Source", typeof(string));
            dt.Columns.Add("ListType", typeof(int));

            // 将数据转换为Excel
            using (var workbook = new XLWorkbook())
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    DataRow dtRow = dt.NewRow();
                    dtRow["Id"] = row["Id"];
                    dtRow["GroupName"] = row["GroupName"];
                    dtRow["AreaName"] = row["AreaName"];
                    if (CategoryConstant.CategoryIntMap.ContainsKey(Convert.ToInt32(row["Category"])))
                    {
                        dtRow["Category"] = CategoryConstant.CategoryIntMap[Convert.ToInt32(row["Category"])];
                    }
                    dtRow["EventType"] = row["EventType"];
                    dtRow["Block"] = row["Block"];
                    dtRow["Source"] = row["Source"];
                    dtRow["ListType"] = row["ListType"];
                    dt.Rows.Add(dtRow);
                }
                var worksheet = workbook.Worksheets.Add(dt, "WhiteList");
                SaveExcelFile(workbook);
            }       
        }

        private void btnGroupSetting_Click(object sender, RoutedEventArgs e)
        {
            GroupSettingWindow win = new GroupSettingWindow();
            win.Show();
        }
    }
}
